<?php
class xl_product_category{
    function read_list(){
        global $dbh;
        $sql='SELECT * FROM product_categories ORDER BY id DESC';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
    function read($id){
        global $dbh;
        $sql='SELECT * FROM product_categories WHERE id=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function read_product($id){
        global $dbh;
        $sql='SELECT id,image,day_time,category_id FROM product WHERE category_id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }
    function read_id_parent($id){
        global $dbh;
        $sql='SELECT * FROM product_categories WHERE parent=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        $category=$sth->fetch(PDO::FETCH_ASSOC);
        return $category;
    }
    function insert($data){
        global $dbh;
        $sql='INSERT INTO product_categories (name,alias,title,keyword,description,parent,location,status,banner,question,introduction,day_time) 
                VALUE (:name,:alias,:title,:keyword,:description,:parent,:location,:status,:banner,:question,:introduction,:day_time)';
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function update($data){
        global $dbh;
        if($data['banner']!=''){
            $sql='UPDATE product_categories SET name=:name,title=:title,keyword=:keyword,description=:description,parent=:parent,location=:location,status=:status,banner=:banner,question=:question,introduction=:introduction,day_time=:day_time WHERE id=:id';
        } else {
            $sql='UPDATE product_categories SET name=:name,title=:title,keyword=:keyword,description=:description,parent=:parent,location=:location,status=:status,question=:question,introduction=:introduction,day_time=:day_time WHERE id=:id';
            unset($data['banner']);
        }
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function updatde_status($id){
        global $dbh;
        $sql='UPDATE product_categories SET status=1-status WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
        
    }
    function test($id,$name){
        global $dbh;
        $sql='SELECT * FROM product_categories WHERE id!=:id AND name=:name';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id,':name'=>$name));
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }
    function test_name($alias){
        global $dbh;
        $sql='SELECT alias FROM product_categories WHERE alias=:alias';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$alias));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function delete_all($id){
        global $dbh;
        $sql='SELECT * FROM product_categories WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function delete($id){
        global $dbh;
        $sql='DELETE FROM product_categories WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
    }
    function delete_product($id){
        global $dbh;
        $sql='DELETE FROM product WHERE category_id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
    }
    function delete_id_parent($id){
        global $dbh;
        $sql='DELETE FROM product_categories WHERE parent=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
    }
    function delete_all_product_images($product_id){
        global $dbh;
        $sql='SELECT product_images.*,product.day_time AS product_day_time FROM product_images,product WHERE product_id=:product_id AND product_images.product_id=product.id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':product_id'=>$product_id));
        return $sth->fetchAll();
    }
    function delete_product_images($product_id){
        global $dbh;
        $sql='DELETE FROM product_images WHERE product_id=:product_id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':product_id'=>$product_id));
        return $sth;
    }
}